﻿Public Class frmProceso
    Dim vectorBlock(100, 2) As String
    Dim VectorDepartamento(100, 5) As String
    Dim vectorFormasDePago(10, 2) As String
    Dim vectorDeuda(,) As String = {}

    Private Sub CargaDatosIniciales()
        listadoBlock()
    End Sub

    Private Sub listadoBlock()
        Dim i As Integer
        conectar()

        cmd.Connection = con
        cmd.CommandText = "SELECT ID,Nombre FROM tBlock"
        cmd.CommandType = CommandType.Text

        dr = cmd.ExecuteReader
        If dr.HasRows Then
            While dr.Read
                vectorBlock(i, 0) = dr("ID").ToString
                vectorBlock(i, 1) = dr("Nombre").ToString
                cbBlock.Items.Add(vectorBlock(i, 1))
                i += 1
            End While

        End If
        dr.Close()

        desconectar()

    End Sub

    Private Sub listadoDepartamento(ByVal IDBlock As Integer)
        Dim i As Integer
        Dim depa As String
        cbDepartamento.Items.Clear()
        conectar()

        cmd.Connection = con
        cmd.CommandText = "SELECT d.ID,d.IDBlock,d.NumeroActual,d.NumeroAnterior,p.Nombre,p.ApPaterno,p.ApMaterno "
        cmd.CommandText += "FROM tDepartamento d INNER JOIN tPersonas p ON p.ID=d.IDPropietario "
        cmd.CommandText += "WHERE p.IDTipoRelacion=1 AND d.IDBlock=" + IDBlock.ToString
        cmd.CommandType = CommandType.Text

        dr = cmd.ExecuteReader
        If dr.HasRows Then
            While dr.Read()
                VectorDepartamento(i, 0) = dr("ID").ToString
                VectorDepartamento(i, 1) = dr("IDBlock").ToString
                VectorDepartamento(i, 2) = dr("NumeroActual").ToString
                VectorDepartamento(i, 3) = dr("NumeroAnterior").ToString
                VectorDepartamento(i, 4) = dr("Nombre").ToString + " " + dr("ApPaterno").ToString + " " + dr("ApMaterno").ToString
                depa = Trim(dr("NumeroActual").ToString) & " EX " & Trim(dr("NumeroAnterior").ToString)
                cbDepartamento.Items.Add(Trim(depa))
                i += 1
                depa = ""
            End While
        End If
        dr.Close()
        desconectar()
    End Sub
    Private Sub btnProcesar_Click(sender As Object, e As EventArgs) Handles btnProcesar.Click
        Dim mensaje, mes, anio, comando, numeroMes, periodo As String
        Dim continuar As Boolean
        Dim vectorDepartamentos(500, 2) As String
        Dim i, fa, largo, idDepa As Integer
        Dim departamento, depaInArray As String
        If Month(dtp.Value) < 10 Then
            numeroMes = "0" & Month(dtp.Value).ToString
        Else
            numeroMes = Month(dtp.Value).ToString
        End If
        anio = Year(dtp.Value).ToString
        mes = MonthName(Month(dtp.Value))
        mensaje = "Se cargarán los datos del mes de " + mes + "/" + anio + ", este proceso es irreversible, realmente desea continuar?"
        If MessageBox.Show(mensaje, "Advertencia", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
            'continuar = verificaProcesoAnterior(numeroMes + "/" + Year(dtp.Value).ToString)
            continuar = True
            If continuar Then
                'MessageBox.Show("ok")
                conectar()
                cmd.Connection = con
                ' CARGAR LOS DATOS DE LOS DEPARTAMENTOS Y SUS VALORES DE GASTOS COMUNES
                periodo = numeroMes.ToString + "/" + anio.ToString
                cmd.CommandText = "SELECT d.ID,g.Total FROM tGruposDeGastos g INNER JOIN tDepartamento d ON d.IDGruposDeGastos = g.ID "
                cmd.CommandText += "WHERE d.ID NOT IN (SELECT IDDepartamentos FROM tgastoscomunes WHERE Periodo='" + periodo + "')"
                If (Trim(cbDepartamento.Text) <> "") Then
                    largo = VectorDepartamento.GetLength(0)
                    For i = 0 To largo - 1
                        departamento = Trim(cbDepartamento.Text.Substring(0, 2))
                        depaInArray = VectorDepartamento(i, 2)
                        If departamento = depaInArray Then
                            idDepa = VectorDepartamento(i, 0)
                            Exit For
                        End If
                    Next
                    If idDepa > 0 Then
                        cmd.CommandText += "AND d.ID = " + idDepa.ToString
                    End If
                End If
                cmd.CommandType = CommandType.Text
                i = 0
                dr = cmd.ExecuteReader
                If dr.HasRows Then
                    While dr.Read
                        vectorDepartamentos(i, 0) = dr("ID").ToString
                        vectorDepartamentos(i, 1) = dr("Total").ToString
                        i += 1
                    End While
                Else
                    MessageBox.Show("El periodo ya fue ingresado con anterioridad, se cancelará el proceso actual.")
                    dr.Close()
                    desconectar()
                    Exit Sub
                End If
                dr.Close()
                desconectar()
                ' CARGAR LOS GASTOS COMUNES DEL MES

                cmd.CommandText = "INSERT INTO tGastosComunes (IDDepartamentos,Periodo,Valor) VALUES "
                cmd.CommandType = CommandType.Text
                comando = ""
                For i = 0 To vectorDepartamentos.GetLength(0) - 1
                    If CInt(vectorDepartamentos(i, 0)) > 0 Then
                        comando += "(" + vectorDepartamentos(i, 0)
                        comando += ",'" + numeroMes + "/" + Year(dtp.Value).ToString + "',"
                        comando += vectorDepartamentos(i, 1) + "),"
                    Else
                        Exit For
                    End If

                Next
                cmd.CommandText += comando.Substring(0, comando.Length - 1)
                conectar()
                cmd.Connection = con


                Try
                    fa = cmd.ExecuteNonQuery()
                Catch ex As Exception

                End Try
                desconectar()
                Dim idDepaDetalle As Integer
                For i = 0 To vectorDepartamentos.GetLength(0) - 1
                    If CInt(vectorDepartamentos(i, 0)) > 0 Then
                        idDepaDetalle = CInt(vectorDepartamentos(i, 0))

                        cmd.CommandText = "INSERT INTO tGastosComunesDetalle(NumReg,IDDepartamentos,Periodo,DetalleConcepto,Concepto,Valor) "

                        cmd.CommandText += "SELECT (@rank:=@rank+1) AS NumReg,gc.IDDepartamentos,gc.Periodo,cg.Nombre,cg.Alias,cg.Valor "
                        cmd.CommandText += "FROM tGastosComunes gc "
                        cmd.CommandText += "INNER JOIN tDepartamento d ON d.ID = gc.IDDepartamentos "
                        cmd.CommandText += "INNER JOIN tGruposDeGastos gg ON gg.ID = d.IDGruposDeGastos "
                        cmd.CommandText += "INNER JOIN tConceptosDeGastos cg ON cg.IDGruposDeGastos = gg.ID "
                        cmd.CommandText += "INNER JOIN (SELECT @rank :=0) b "
                        cmd.CommandText += "WHERE d.ID =" & idDepaDetalle.ToString & " AND GC.Periodo='" & periodo & "'"
                        conectar()
                        cmd.Connection = con
                        Try
                            fa = cmd.ExecuteNonQuery()
                        Catch ex As Exception

                        End Try
                        desconectar()
                    Else
                        Exit For
                    End If

                    
                    If fa <= 0 Then
                        MessageBox.Show("Error al ingresar detalle. Id:" + idDepaDetalle.ToString + ", Periodo: " + periodo + ".")
                    End If

                Next
                



                If fa > 0 Then
                    MessageBox.Show("Se cargaron correctamente " + fa.ToString + " gastos comunes")
                Else
                    MessageBox.Show("No se pudieron cargar los gastos comunes.")
                End If
                dr.Close()
                desconectar()


            End If
        End If



    End Sub

    Private Function verificaProcesoAnterior(ByVal periodo As String) As Boolean
        Dim resultado As Boolean

        conectar()
        cmd.Connection = con
        cmd.CommandText = "SELECT * FROM tGastosComunes WHERE Periodo ='" + periodo + "'"

        cmd.CommandType = CommandType.Text

        dr = cmd.ExecuteReader
        If dr.HasRows Then
            MessageBox.Show("El periodo ya fue procesado, se cancelará el proceso actual, para más información contacte a su administrador del sistema")
            resultado = False
        Else
            resultado = True
        End If
        dr.Close()
        desconectar()
        Return resultado
    End Function

    Private Sub frmProceso_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        CargaDatosIniciales()
    End Sub

    Private Sub cbBlock_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cbBlock.SelectedIndexChanged
        listadoDepartamento(CInt(cbBlock.Text))
    End Sub

    Private Sub cbDepartamento_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cbDepartamento.SelectedIndexChanged

    End Sub
End Class